package cn.sylinx.horm.dialect.sql;

import cn.sylinx.horm.config.OrmConfigHolder;
import cn.sylinx.horm.exception.HORMException;
import cn.sylinx.horm.model.anno.PrimaryKey;
import cn.sylinx.horm.model.base.BaseModel;
import cn.sylinx.horm.model.base.Model;
import cn.sylinx.horm.model.cache.ModelCacheUtil;
import cn.sylinx.horm.model.cache.ModelFabric;
import cn.sylinx.horm.model.optlock.OptimisticLockInsert;
import cn.sylinx.horm.model.optlock.OptimisticLockUpdate;
import cn.sylinx.horm.model.optlock.OptimisticLockWrapper;
import cn.sylinx.horm.type.handler.TypeHandler;
import cn.sylinx.horm.util.GLog;
import cn.sylinx.horm.util.Pair;
import cn.sylinx.horm.util.StrKit;
import cn.sylinx.horm.util.Tuple;

import java.lang.reflect.Field;
import java.util.ArrayList;
import java.util.Collections;
import java.util.Date;
import java.util.HashMap;
import java.util.HashSet;
import java.util.List;
import java.util.Map;
import java.util.Map.Entry;
import java.util.Set;

public class DefaultSqlBuilder extends SqlBuilder {

    private String escape0 = "";
    private String escape1 = "";
    private boolean toUpperCase = false;

    public DefaultSqlBuilder() {
        String[] escapes = getEscapeChar();
        if (escapes != null && escapes.length >= 2) {
            escape0 = escapes[0];
            escape1 = escapes[1];
        }
        toUpperCase = toUpperCase();
    }

    protected String[] getEscapeChar() {
        return new String[]{"", ""};
    }

    protected boolean toUpperCase() {
        return false;
    }

    protected String convertColumn(String column) {
        return toUpperCase ? column.toUpperCase() : column;
    }

    protected String convertTable(String table) {
        return toUpperCase ? table.toUpperCase() : table;
    }

    @Override
    public Tuple buildPaginatorSql(String preSql, int pageNumber, int pageSize) {

        String sqlCount = "SELECT COUNT(1) AS totalCount FROM (" + preSql + ") AS temp";
        String sql = preSql + " LIMIT ?, ? ";
        int offset = pageSize * (pageNumber - 1);
        Object[] params = new Object[]{offset, pageSize};
        return Tuple.apply(sqlCount, sql, params);
    }

    protected List<String> getPrimaryKey(final PrimaryKey pks) {
        List<String> pklist = new ArrayList<>();
        if (pks != null) {
            String[] pkArray = pks.value();
            for (String pk : pkArray) {
                if (StrKit.isNotBlank(pk)) {
                    pklist.add(convertColumn(pk));
                }
            }
        }

        if (pklist.isEmpty()) {
            pklist.add(convertColumn("id"));
        }
        return pklist;
    }

    @Override
    public <T> Pair buildDeleteSQL(T t) {

        if (t == null) {
            throw new HORMException("model is null");
        }

        ModelFabric modelFabric = ModelCacheUtil.getModelFabric(t.getClass());
        String tableName = convertTable(modelFabric.getTableName());
        final PrimaryKey pk = modelFabric.getPrimaryKey();
        List<String> pks = getPrimaryKey(pk);
        Map<String, String> map = modelFabric.getAttrMapping();
        Map<String, TypeHandler<?>> typeHandlerMap = modelFabric.getTypeHandlerMap();

        final String token = " AND ";

        // 获取所有字段
        List<Field> fields = ModelCacheUtil.getObjectAllFieldsWithcache(t.getClass());
        StringBuilder sql = new StringBuilder("DELETE FROM " + escape0 + tableName + escape1 + " WHERE ");

        boolean find = false;
        List<Object> params = new ArrayList<>();

        try {

            for (Field item : fields) {

                String fieldName = item.getName();

                if (pks.contains(fieldName)) {
                    // 字段
                    String f = map.get(fieldName);
                    item.setAccessible(true);
                    Object v = item.get(t);

                    if (f != null && v != null) {
                        find = true;
                        TypeHandler<?> typeHandler = typeHandlerMap.get(fieldName);
                        sql.append(escape0).append(convertColumn(f)).append(escape1).append(" = ? ").append(token);
                        params.add(convertValue(typeHandler, v));
                    }
                }
            }

        } catch (IllegalAccessException e) {
            throw new HORMException(e);
        }

        if (!find) {
            throw new HORMException("primary key has no value");
        }

        int totalLen = sql.length();
        int index = sql.length() - token.length();
        sql.delete(index, totalLen);

        Object[] objects = new Object[params.size()];
        for (int i = 0; i < params.size(); ++i) {
            objects[i] = params.get(i);
        }

        String sqlNative = sql.toString();

        return Pair.apply(sqlNative, objects);
    }

    @Override
    public <T> Pair buildInsertSQL(T t) {

        if (t == null) {
            throw new HORMException("model is null");
        }

        ModelFabric mf = ModelCacheUtil.getModelFabric(t.getClass());
        Map<String, String> map = mf.getAttrMapping();
        String tableName = mf.getTableName();

        if (map == null || map.isEmpty()) {
            GLog.error("insert mapper is empty");
            throw new HORMException("insert mapper is empty");
        }

        if (StrKit.isBlank(tableName)) {
            GLog.error("table name is empty");
            throw new HORMException("table name is empty");
        }

        tableName = convertTable(tableName);

        // 获取所有字段
        List<Field> fields = ModelCacheUtil.getObjectAllFieldsWithcache(t.getClass());

        if (fields.isEmpty()) {
            GLog.error("no fields");
            throw new HORMException("no fields");
        }

        Map<String, TypeHandler<?>> typeHandlerMap = mf.getTypeHandlerMap();

        StringBuilder fds = new StringBuilder();
        StringBuilder fdsv = new StringBuilder();
        List<Object> params = new ArrayList<>();

        fds.append("(");
        fdsv.append("(");

        OptimisticLockInsert optimisticLockInsert = OptimisticLockWrapper.parseOptimisticLockInsert(t);

        try {

            if (optimisticLockInsert != null) {
                optimisticLockInsert.getVersionField().setAccessible(true);
                optimisticLockInsert.getVersionField().set(t, optimisticLockInsert.getInitVersionValue());
            }

            for (Field item : fields) {
                String fieldName = item.getName();
                if (map.containsKey(fieldName)) {
                    item.setAccessible(true);
                    Object v = item.get(t);
                    // 值不为空才插入
                    if (v != null) {
                        String jdbcFieldName = map.get(fieldName);
                        TypeHandler<?> typeHandler = typeHandlerMap.get(fieldName);
                        fds.append(escape0).append(convertColumn(jdbcFieldName)).append(escape1).append(",");
                        fdsv.append("?,");
                        params.add(convertValue(typeHandler, v));
                    }
                }
            }
        } catch (IllegalAccessException e) {
            throw new HORMException(e);
        }

        // 存在插入字段
        if (fds.length() > 1) {

            fds.deleteCharAt(fds.length() - 1);
            fdsv.deleteCharAt(fdsv.length() - 1);
            fds.append(")");
            fdsv.append(")");

        } else {

            GLog.error("no insert fields");
            throw new HORMException("no insert fields");
        }

        String sb = "INSERT INTO " + escape0 + tableName + escape1 + fds + " VALUES " +
                fdsv;

        Object[] objects = new Object[params.size()];
        for (int i = 0; i < params.size(); ++i) {
            objects[i] = params.get(i);
        }

        return Pair.apply(sb, objects);
    }

    @Override
    public <T> Pair buildBatchInsertSQL(List<T> dataList) {

        if (dataList == null || dataList.isEmpty()) {
            throw new HORMException("batch data error");
        }

        Object presentObject = dataList.get(0);
        boolean addGmtCreateValue = presentObject instanceof BaseModel;

        Class<?> objectClass = presentObject.getClass();
        ModelFabric mf = ModelCacheUtil.getModelFabric(objectClass);
        String tableName = convertTable(mf.getTableName());

        Map<String, Field> attrFields = mf.getFieldMap();
        Map<String, String> jdbcMap = mf.getJdbcMapping();
        Map<String, TypeHandler<?>> typeHandlerMap = mf.getTypeHandlerMap();

        List<String> columns = new ArrayList<>();
        Set<String> columnSet = jdbcMap.keySet();

        // 所有列
        if (OrmConfigHolder.isCaseSensitive()) {
            columns.addAll(columnSet);
        } else {
            for (String c : columnSet) {
                String cupper = c.toUpperCase();
                if (!columns.contains(cupper)) {
                    columns.add(cupper);
                }
            }
        }

        // 解析sql
        StringBuilder sb = new StringBuilder();
        sb.append("INSERT INTO ").append(escape0).append(tableName).append(escape1).append(" (");
        StringBuilder placeholders = new StringBuilder();
        for (String column : columns) {
            sb.append(escape0).append(convertColumn(column)).append(escape1).append(",");
            placeholders.append("?,");
        }
        sb.deleteCharAt(sb.length() - 1);
        placeholders.deleteCharAt(placeholders.length() - 1);
        sb.append(") VALUES (").append(placeholders).append(")");
        String insertSql = sb.toString();

        // 所有值
        List<Object[]> params = new ArrayList<>();
        for (T t : dataList) {

            if (addGmtCreateValue) {
                ((BaseModel) t).setGmtCreate(new Date());
            }

            Object[] row = new Object[columns.size()];

            for (int i = 0; i < columns.size(); ++i) {
                // jdbc 字段找到 attr
                String attr = jdbcMap.get(columns.get(i));
                // 通过 attr找到字段
                Field f = attrFields.get(attr);
                TypeHandler<?> typeHanlder = typeHandlerMap.get(f.getName());
                // 获取字段值
                row[i] = getObjectValue(typeHanlder, f, t);
            }

            params.add(row);
        }

        return Pair.apply(insertSql, params);
    }

    private Object getObjectValue(TypeHandler<?> typeHanlder, Field f, Object t) {

        boolean preAccess = f.isAccessible();
        try {
            f.setAccessible(true);
            return convertValue(typeHanlder, f.get(t));
        } catch (Exception e) {
            GLog.error("getObjectValue error" + e, e);
        } finally {
            f.setAccessible(preAccess);
        }

        return null;
    }

    @Override
    public <T> Pair buildUpdateSQL(T t) {

        if (t == null) {
            throw new HORMException("model is null");
        }

        ModelFabric mf = ModelCacheUtil.getModelFabric(t.getClass());
        Map<String, String> map = mf.getAttrMapping();
        List<String> pks = getPrimaryKey(mf.getPrimaryKey());
        String tableName = mf.getTableName();

        if (map == null || map.isEmpty()) {
            GLog.error("update mapper is empty");
            return null;
        }

        if (StrKit.isBlank(tableName)) {
            GLog.error("table name is empty");
            return null;
        }

        tableName = convertTable(tableName);

        if (pks == null) {
            pks = Collections.emptyList();
        }

        // 获取所有字段
        List<Field> fields = ModelCacheUtil.getObjectAllFieldsWithcache(t.getClass());

        if (fields.isEmpty()) {
            return null;
        }

        // 主键
        Map<String, Object> pkKv = new HashMap<>();
        Map<String, TypeHandler<?>> typeHandlerMap = mf.getTypeHandlerMap();

        StringBuilder fds = new StringBuilder();
        List<Object> params = new ArrayList<>();

        // 获取可设置空字段值
        Set<String> nullableSets = new HashSet<>();
        if (t instanceof Model) {
            nullableSets = ((Model) t).getNullableFields();
        }

        // 乐观锁信息
        OptimisticLockUpdate olu = OptimisticLockWrapper.parseOptimisticLockUpdate(t);

        try {

            if (olu != null) {
                olu.getVersionField().setAccessible(true);
                olu.getVersionField().set(t, olu.getNewVersionValue());
            }

            for (Field item : fields) {

                item.setAccessible(true);
                String fieldName = item.getName();

                // 判断主键
                if (pks.contains(fieldName)) {

                    Object pkValue = item.get(t);
                    String columnKey = map.get(fieldName);
                    pkKv.put(columnKey, pkValue);

                } else {
                    // 字段
                    String f = map.get(fieldName);
                    Object v = item.get(t);

                    if (f != null) {// 找到字段

                        boolean nullable = nullableSets.contains(fieldName);// 可为空

                        if (v != null) {
                            TypeHandler<?> typeHandler = typeHandlerMap.get(fieldName);
                            fds.append(escape0).append(convertColumn(f)).append(escape1).append(" = ?,");
                            params.add(convertValue(typeHandler, v));
                        } else if (nullable) {
                            // 可为空，则设置null值
                            fds.append(escape0).append(convertColumn(f)).append(escape1).append(" = NULL,");
                        }

                    }

                }
            }
        } catch (IllegalAccessException e) {
            throw new HORMException(e);
        }

        // 存在插入字段
        if (fds.length() > 0) {

            fds.deleteCharAt(fds.length() - 1);

        } else {

            throw new RuntimeException("update values is empty ");
        }

        StringBuilder sb = new StringBuilder();
        sb.append("UPDATE ").append(escape0).append(tableName).append(escape1).append(" SET ").append(fds);

        if (!pkKv.isEmpty()) {

            final String token = " AND ";

            sb.append(" WHERE ");

            if (pks.size() == pkKv.size()) {
                // 全部匹配主键，按顺序组合

                for (String k : pks) {
                    String c = map.get(k);
                    sb.append(escape0).append(convertColumn(c)).append(escape1).append(" = ?").append(token);
                    params.add(pkKv.get(c));
                }

            } else {

                for (Entry<String, Object> entry : pkKv.entrySet()) {
                    sb.append(escape0).append(convertColumn(entry.getKey())).append(escape1).append(" = ?")
                            .append(token);
                    params.add(entry.getValue());
                }
            }

            // 此处加入乐观锁
            if (olu != null) {
                String columnKey = map.get(olu.getVersionFieldAttr());
                sb.append(escape0).append(convertColumn(columnKey)).append(escape1).append(" = ?").append(token);
                params.add(olu.getOldVersionValue());
            }

            int totalLen = sb.length();
            int index = sb.length() - token.length();
            sb.delete(index, totalLen);
        }

        Object[] objects = new Object[params.size()];
        for (int i = 0; i < params.size(); ++i) {
            objects[i] = params.get(i);
        }

        String sql = sb.toString();

        return Pair.apply(sql, objects);
    }

    @Override
    public Pair buildDeleteByFieldSQL(List<Pair> kvList, Class<?> clz) {

        ModelFabric mf = ModelCacheUtil.getModelFabric(clz);

        String table = mf.getTableName();
        if (table == null) {
            throw new HORMException("对象没有Table注解");
        }

        Map<String, String> am = mf.getAttrMapping();
        if (am == null) {
            throw new HORMException("对象映射字段缺失");
        }

        table = convertTable(table);

        String andToken = " AND ";
        StringBuilder sb = new StringBuilder();
        List<Object> paramList = new ArrayList<>();

        for (Pair kv : kvList) {
            String field = kv.getFirst();
            Object value = kv.getSecond();
            String trueField = am.get(field);
            if (trueField == null) {
                trueField = field;
            }
            if (value == null) {
                sb.append(escape0).append(convertColumn(trueField)).append(escape1).append(" IS NULL").append(andToken);
            } else {
                sb.append(escape0).append(convertColumn(trueField)).append(escape1).append(" = ?").append(andToken);
                paramList.add(value);
            }

        }
        sb.delete(sb.length() - andToken.length(), sb.length());
        String sql = "DELETE FROM " + escape0 + table + escape1 + " WHERE " + sb;

        return Pair.apply(sql, paramList.toArray());
    }

    @Override
    public Pair buildQueryByFieldSQL(List<Pair> kvList, Class<?> clz) {

        return buildQueryByFieldSQL(kvList, clz, null);
    }

    @Override
    public Pair buildQueryByFieldSQL(List<Pair> kvList, Class<?> clz, String[] fields) {

        ModelFabric mf = ModelCacheUtil.getModelFabric(clz);

        String table = mf.getTableName();
        if (table == null) {
            throw new HORMException("对象没有Table注解");
        }

        Map<String, String> am = mf.getAttrMapping();
        if (am == null) {
            throw new HORMException("对象映射字段缺失");
        }
        table = convertTable(table);

        String andToken = " AND ";
        StringBuilder sb = new StringBuilder();

        List<Object> paramList = new ArrayList<>();

        for (Pair kv : kvList) {
            String field = kv.getFirst();
            Object value = kv.getSecond();

            String trueField = am.get(field);
            if (trueField == null) {
                trueField = field;
            }
            if (value == null) {
                sb.append(escape0).append(convertColumn(trueField)).append(escape1).append(" IS NULL").append(andToken);
            } else {
                sb.append(escape0).append(convertColumn(trueField)).append(escape1).append(" = ?").append(andToken);
                paramList.add(value);
            }

        }
        sb.delete(sb.length() - andToken.length(), sb.length());

        // 选择字段
        StringBuilder sf = new StringBuilder();
        if (fields != null && fields.length > 0) {
            for (String field : fields) {
                String trueField = am.get(field);
                if (trueField == null) {
                    trueField = field;
                }
                sf.append(escape0).append(convertColumn(trueField)).append(escape1).append(",");
            }
            sf.deleteCharAt(sf.length() - 1);
        } else {
            sf.append("*");
        }

        String sql = "SELECT " + sf + " FROM " + escape0 + table + escape1 + " WHERE " + sb;

        return Pair.apply(sql, paramList.toArray());
    }

    @Override
    public String buildSimpleQuery(Class<?> clz) {
        return buildSimpleQuery(clz, null);
    }

    @Override
    public String buildSimpleQuery(Class<?> clz, String[] fields) {

        ModelFabric mf = ModelCacheUtil.getModelFabric(clz);
        String table = mf.getTableName();
        if (table == null) {
            throw new RuntimeException("对象没有Table注解");
        }
        table = convertTable(table);

        Map<String, String> am = mf.getAttrMapping();
        if (fields == null || fields.length == 0 || am == null) {
            return "SELECT * FROM " + escape0 + table + escape1;
        }

        StringBuilder sb = new StringBuilder("SELECT ");
        for (String field : fields) {
            String trueField = am.get(field);
            if (trueField == null) {
                trueField = field;
            }
            sb.append(escape0).append(convertColumn(trueField)).append(escape1).append(",");
        }
        sb.deleteCharAt(sb.length() - 1);
        sb.append(" FROM ").append(escape0).append(table).append(escape1);
        return sb.toString();
    }

    @Override
    public String buildValidateQuery() {
        return "SELECT 1";
    }

    @Override
    public String getTable(Class<?> clz) {
        return ModelCacheUtil.getModelFabric(clz).getTableName();
    }

    @Override
    public String getTableColumn(Class<?> clz, String prop) {
        ModelFabric mf = ModelCacheUtil.getModelFabric(clz);
        Map<String, String> attrMap = mf.getAttrMapping();
        String column = attrMap.get(prop);
        if (column == null) {
            column = prop;
        }
        return column;
    }

    @Override
    public String[] buildCreateTableDDL(Class<?> clz) {
        throw new UnsupportedOperationException("not implement");
    }

    protected String getRawMappingAttr(String f, String preEscape, String postEscape) {
        if (preEscape == null || preEscape.trim().isEmpty()) {
            return f;
        }

        if (postEscape == null || postEscape.trim().isEmpty()) {
            return f;
        }

        String fact = f;
        String preEscapeAct = preEscape.trim();
        if (fact.startsWith(preEscapeAct)) {
            fact = fact.substring(preEscapeAct.length());
        }

        String postEscapeAct = postEscape.trim();
        if (fact.endsWith(postEscapeAct)) {
            fact = fact.substring(0, fact.length() - postEscapeAct.length());
        }

        return fact;
    }
}
